﻿Imports System.Data.SqlClient

Public Class frmInsuranceCompaniesNew

    Sub Clear()
        Me.ErrProv.Clear()
        Me.txtName.Clear()
        Me.txtTele.Clear()
        Me.txtEmail.Clear()
        Me.txtAddress.Clear()
        Me.txtAbbreviation.Clear()
        Me.txtAccNoPrem.Clear()
        Me.GridContacts.Rows.Clear()
        Me.txtName.Focus()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If Me.txtName.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtName, "Please fill in all details")
            Me.txtName.Focus()

        ElseIf Me.txtTele.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtTele, "Please fill in all details")
            Me.txtTele.Focus()

        ElseIf Me.txtAddress.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtEmail, "Please fill in all details")
            Me.txtEmail.Focus()

        ElseIf Me.txtEmail.Text.Trim.Length > 0 And
           Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtEmail.Text.Trim, _
                                                            "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
            Me.ErrProv.SetError(Me.txtEmail, "Please enter a valid email address or leave blank")
            Me.txtEmail.Focus()
            Exit Sub

        ElseIf Me.txtAbbreviation.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAbbreviation, "Please fill in all details")
            Me.txtAbbreviation.Focus()

        ElseIf Me.txtAccNamePrem.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAccNoPrem, "Please fill in all details")
            Me.txtAccNoPrem.Focus()

        ElseIf Me.txtAccNameComm.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAccNoComm, "Please fill in all details")
            Me.txtAccNoComm.Focus()
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim SNo As Integer

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                cmd.CommandText = "Insert Into InsuranceCompanies (CompanyName,Tele1,Abbreviation,Email,Address,AccNoPrem,AccNoComm) Values " & _
                                  "(@CompanyName,@Tele1,@Abbreviation,@Email,@Address,@AccNoPrem,@AccNoComm) Select SCOPE_IDENTITY()"

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@CompanyName", Me.txtName.Text.Trim)
                cmd.Parameters.AddWithValue("@Tele1", Me.txtTele.Text.Trim)
                cmd.Parameters.AddWithValue("@Abbreviation", Me.txtAbbreviation.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@AccNoPrem", Me.txtAccNoPrem.Text.Trim)
                cmd.Parameters.AddWithValue("@AccNoComm", Me.txtAccNoComm.Text.Trim)
                SNo = CInt(cmd.ExecuteScalar)

                For Each row As DataGridViewRow In GridContacts.Rows
                    If Not row.IsNewRow Then
                        cmd.CommandText = "Insert Into InsuranceCompanyContacts (CompanyID,ContactName,ContactPosition,ContactEmail,ContactMobileNo,Branch,ContactTele,SavedUser)" & _
                                          " Values (@CompanyID,@ContactName,@ContactPosition,@ContactEmail,@ContactMobileNo,@Branch,@ContactTele,@SavedUser)"
                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@CompanyID", SNo)
                        cmd.Parameters.AddWithValue("@ContactName", row.Cells(Me.ContactPerson.Index).Value)
                        cmd.Parameters.AddWithValue("@ContactPosition", row.Cells(Me.Position.Index).Value)
                        cmd.Parameters.AddWithValue("@ContactEmail", row.Cells(Me.Email.Index).Value)
                        cmd.Parameters.AddWithValue("@ContactMobileNo", row.Cells(Me.ColMobile.Index).Value)
                        cmd.Parameters.AddWithValue("@Branch", row.Cells(Me.DGColBranch.Index).Value)
                        cmd.Parameters.AddWithValue("@ContactTele", row.Cells(Me.Tele.Index).Value)
                        cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully!")

                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridContactPersons_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridContacts.CellClick
        If e.ColumnIndex = Me.ColDelete.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Me.GridContacts.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
        Clear()
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSelectAcc_Click(sender As System.Object, e As System.EventArgs) Handles btnSelectAccPrem.Click
        Dim a As New frmSelectAccount
        a.ShowDialog()

        If SelAccNo <> "" Then
            Me.txtAccNoPrem.Text = SelAccNo
            Me.txtAccNamePrem.Text = SelAccName
        End If
    End Sub

    Private Sub txtAccNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAccNoPrem.TextChanged
        Me.txtAccNamePrem.Clear()
    End Sub

    Private Sub txtAccNo_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAccNoPrem.KeyUp
        If e.KeyCode = Keys.Enter Then
            Me.txtAccNamePrem.Text = GetAccName(Me.txtAccNoPrem.Text.Trim)
        End If
    End Sub

    Private Sub btnSelectAccComm_Click(sender As System.Object, e As System.EventArgs) Handles btnSelectAccComm.Click
        Dim a As New frmSelectAccount
        a.ShowDialog()

        If SelAccNo <> "" Then
            Me.txtAccNoComm.Text = SelAccNo
            Me.txtAccNameComm.Text = SelAccName
        End If
    End Sub

    Private Sub txtAccNoComm_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAccNoComm.TextChanged
        Me.txtAccNameComm.Clear()
    End Sub

    Private Sub txtAccNoComm_KeyUp(sender As System.Object, e As System.Windows.Forms.KeyEventArgs) Handles txtAccNoComm.KeyUp
        If e.KeyCode = Keys.Enter Then
            Me.txtAccNameComm.Text = GetAccName(Me.txtAccNoComm.Text.Trim)
        End If
    End Sub
End Class